<?php

namespace app\admin\controller;

use app\admin\model\system\SysdepartmentModel;
use app\admin\model\system\SysprojectModel;
use app\common\service\system\SysmediatypeService;
use think\facade\Db;

class PortalController extends AdminCommonController
{
    public function index()
    {
        // var_dump(session());
        return $this->fetch();
    }

    public function setSession()
    {
        $project_id = input('projectid');
        $project = SysprojectModel::yqGetOne($project_id);
        session('projectid', $project_id);
        session('projectname', $project['name']);
        ////////////更新未到的状态
        /*$conupdate['hospitalid']=I('hospitalid');
        $conupdate['projectid']=I('projectid');
        $conupdate['status']=array('in',array('等待','回访'));
        $patientlist_update=$patient->where($con)->select();
        foreach ($patientlist_update as $key => $value) {
        if(strtotime($value['yuyuetime'])<time()){
        $data_update['id']=$value['id'];
        $data_update['status']='未到';
        $patient->save($data_update);
        }
        }*/
        if (session('sysuser.fenlei') == '咨询') {
            // $patient = M('Patient');
            // $con['hospitalid'] = I('hospitalid');
            // $con['projectid'] = I('projectid');
            // $con['isdel'] = array('neq', 1);
            // $con['status'] = '回访';
            // $userpart = session('sysuser.part');
            // $con['partid'] = $userpart['id'];
            // $patientlist = $patient->field('id')->where($con)->select();
            // if (!empty($patientlist)) {
            //     foreach ($patientlist as $key => $value) {
            //         $patientids[] = $value['id'];
            //     }
            //     // dump($patientlist);
            //     // exit;
            //     $inform = M('Patientinform');
            //     $con['patientid'] = array('in', $patientids);
            //     $con['nexttime'] = array('like', date('Y-m-d') . '%');
            //     $informcount = $inform->where($con)->count();
            // } else {
            //     $informcount = null;
            // }
            // if (empty($informcount)) {
            //     $informcount = 0;
            // }
            $this->success('首页切换成功！本今天有<font size="50" color="red">' . 0 . '</font>个回访提醒！请打开预约登记列表查看！');
        } else if (session('sysuser.fenlei') == '医院') {
            // $condition['projectid'] = $project_id;
            // $condition['isdel'] = array('neq', 1);
            // /*$patient=M('Yypatient');
            //
            // $otherpartids=session('sysuser.otherpartids');
            // if(!empty($otherpartids)){
            // $condition['partid']=array('in',$otherpartids);
            // }else{
            // $condition['userid']=session('sysuser.id');
            // }
            //
            // $patientlist=$patient->field('id')->where($con)->select();
            // foreach ($patientlist as $key => $value) {
            // $patientids[]=$value['id'];
            // }
            //  */
            // $otherpartids = session('sysuser.otherpartids');
            // if (!empty($otherpartids)) {
            //     $condition['partid'] = array('in', $otherpartids);
            //     $user = M('user');
            //     $userids = $user->where($condition)->getField('id', true);
            // } else {
            //     $userids = session('sysuser.id');
            // }
            // $huifang = M('yypatienthuifang');
            // $con['userid'] = array('in', $userids);
            // $con['nexttime'] = array('like', date('Y-m-d') . '%');
            // $yypatientids = $huifang->where($con)->group('yypatientid')->getField('yypatientid', true);
            // if (!empty($yypatientids)) {
            //     $tempcon['id'] = array('in', $yypatientids);
            // }
            // $tempcon['fuzhentime'] = array('like', date('Y-m-d') . '%');
            // $tempcon['_logic'] = 'or';
            // $condition['_complex'] = $tempcon;
            // $condition['userid'] = array('in', $userids);
            // $count = M('yypatient')->where($condition)->count();
            // if (empty($count)) {
            //     $count = 0;
            // }
            $this->success('首页切换成功！本今天有<font size="50" color="red">' . 0 . '</font>个回访提醒！请打开档案管理列表查看！');
        } else {
            $this->success('首页切换成功！');
        }
    }

    public function getIndexDataYuyue()
    {
        $projectid = session('projectid');
        $mediatypeids_project = SysmediatypeService::getIdsInProject($projectid);
        $mediatypeids_department = SysmediatypeService::getIdsInDepartment(session('sysuser.department_id'));
        // var_dump($mediatypeids_project);
        // $meidatypeid1 = explode(',', $mediatypeids_department);
        // $meidatypeid2 = explode(',', $mediatypeids_project);
        $meidatypeid = array_intersect($mediatypeids_project, $mediatypeids_department);
        $meidatypeid = implode(',', $meidatypeid);
        if(empty($meidatypeid)){
            $this->error("请联系管理员给所属部门选择渠道和媒体！");
        }
        // $con['id'] = array('in', $meidatypeid);
        // $meidatypelist = M('Mediatype')->cache(60 * 60 * 12)->where($con)->order('create_time asc')->select();
        $sqlmedia = "SELECT sysmediatype.`id` as id,sysmediatype.`name`,sysmediatype.`ename`,
            COUNT(CASE 
                WHEN	casetable.`create_time` BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `jinriyuyue`,
            COUNT(CASE 
                WHEN	casetable.`yuyuetime` BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `jinriyudao`,
            COUNT(CASE 
                WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `jinrishidao`,
            COUNT(CASE 
                WHEN	casetable.`create_time` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') 
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `zuoriyuyue`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') 
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `zuoriyudao`,
            COUNT(CASE 
                WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') 
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `zuorishidao`,
            COUNT(CASE 
                WHEN	casetable.`create_time` BETWEEN DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `benyueyuyue`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `benyueyudao`,
            COUNT(CASE 
                WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `benyueshidao`,
            COUNT(CASE 
                WHEN	casetable.`create_time` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `tongbiyuyue`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `tongbiyudao`,
            COUNT(CASE 
                WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `tongbishidao`,
            COUNT(CASE 
                WHEN	casetable.`create_time` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `shangyueyuyue`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `shangyueyudao`,
            COUNT(CASE 
                WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `shangyueshidao`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 00:00:00')
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `mingtianyudao`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -2 DAY), '%Y-%m-%d 00:00:00')
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -2 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `houtianyudao`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -3 DAY), '%Y-%m-%d 00:00:00')
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -3 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `waitianyudao`
        FROM `case` as casetable,`sysmediatype`
        WHERE casetable.`isdel` = 1  AND casetable.project_id=" . $projectid . " AND casetable.mediatype_id=sysmediatype.id and casetable.mediatype_id in(" . $meidatypeid . ")
        GROUP BY `mediatype_id`";
        // $listmediatype=M()->cache(60)->query($sqlmedia);
        $sqlsex = "SELECT
            `mediatype_id` as id,case 'sex' 
                when sex='男' then '网络妇科'
                when sex='女' then '网络男科'
            end as name,case 'sex' 
                when sex='女' then 'nanke'
                when sex='男' then 'fuke'
            end as ename,
            COUNT(CASE 
                WHEN	`create_time` BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `jinriyuyue`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `jinriyudao`,
            COUNT(CASE 
                WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `jinrishidao`,
            COUNT(CASE 
                WHEN	`create_time` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') 
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `zuoriyuyue`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') 
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `zuoriyudao`,
            COUNT(CASE 
                WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') 
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `zuorishidao`,
            COUNT(CASE 
                WHEN	`create_time` BETWEEN DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `benyueyuyue`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `benyueyudao`,
            COUNT(CASE 
                WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `benyueshidao`,
            COUNT(CASE 
                WHEN	`create_time` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `tongbiyuyue`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `tongbiyudao`,
            COUNT(CASE 
                WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `tongbishidao`,
            COUNT(CASE 
                WHEN	`create_time` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `shangyueyuyue`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `shangyueyudao`,
            COUNT(CASE 
                WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                      AND DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `shangyueshidao`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 00:00:00')
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `mingtianyudao`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -2 DAY), '%Y-%m-%d 00:00:00')
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -2 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `houtianyudao`,
            COUNT(CASE 
                WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -3 DAY), '%Y-%m-%d 00:00:00')
                      AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -3 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
              END) AS `waitianyudao`
        FROM `case` 
        WHERE 
            `isdel` = 1  AND project_id=" . $projectid . " AND mediatype_id=1
        GROUP BY `sex`";
        // $listsex=M()->cache(0)->query($sqlsex);
        ///////////////////////////////////////////////////////////
        /*$wangluozong = $listmediatype[0];
        unset($listmediatype[0]);
        if (in_array(1, explode(',',$meidatypeid))) {
            if ($projectid != 19) {
                array_unshift($listmediatype, $listsex[1], $listsex[0]);
            }
        }
        array_unshift($listmediatype, $wangluozong);*/
        $sqltotal = "SELECT
              concat('0') as id,concat('总数据') as name,concat('total') as ename,
              COUNT(CASE 
                  WHEN	`create_time` BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `jinriyuyue`,
              COUNT(CASE 
                  WHEN	`yuyuetime` BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `jinriyudao`,
              COUNT(CASE 
                  WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT(NOW(),'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(),'%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `jinrishidao`,
              COUNT(CASE 
                  WHEN	`create_time` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') 
                        AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `zuoriyuyue`,
              COUNT(CASE 
                  WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') 
                        AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `zuoriyudao`,
              COUNT(CASE 
                  WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') 
                        AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `zuorishidao`,
              COUNT(CASE 
                  WHEN	`create_time` BETWEEN DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')
                        AND DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `benyueyuyue`,
              COUNT(CASE 
                  WHEN	`yuyuetime` BETWEEN DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')
                        AND DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `benyueyudao`,
              COUNT(CASE 
                  WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( CURDATE(), '%Y-%m-01 00:00:00')
                        AND DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `benyueshidao`,
              COUNT(CASE 
                  WHEN	`create_time` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                        AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `tongbiyuyue`,
              COUNT(CASE 
                  WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                        AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `tongbiyudao`,
              COUNT(CASE 
                  WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                        AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `tongbishidao`,
              COUNT(CASE 
                  WHEN	`create_time` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                        AND DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `shangyueyuyue`,
              COUNT(CASE 
                  WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                        AND DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `shangyueyudao`,
              COUNT(CASE 
                  WHEN	`statusdao` = '已到' AND `yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
                        AND DATE_FORMAT( LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `shangyueshidao`,
              COUNT(CASE 
                  WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 00:00:00')
                        AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `mingtianyudao`,
              COUNT(CASE 
                  WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -2 DAY), '%Y-%m-%d 00:00:00')
                        AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -2 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `houtianyudao`,
              COUNT(CASE 
                  WHEN	`yuyuetime` BETWEEN DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -3 DAY), '%Y-%m-%d 00:00:00')
                        AND DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL -3 DAY), '%Y-%m-%d 23:59:59') THEN TRUE 
                END) AS `waitianyudao`
          FROM `case` 
          WHERE 
              `isdel` = 1 and mediatype_id in(" . $meidatypeid . ")  AND project_id=" . $projectid . "";
        // var_dump($sqltotal);
        // var_dump($sqlmedia);
        if (session('sysuser.isdatayuyuetotal') == "是") {
            // $listtotal = Db::query($sqltotal);
            // if(is_array($listtotal)){
            //     array_unshift($listmediatype, $listtotal[0]);
            // }
            $listtotal = Db::query($sqltotal . " union all " . $sqlmedia);
        } else {
            $sqltotal = "";
            $listtotal = Db::query($sqlmedia);
        }
        // $listtotal = M()->cache(0)->query($sqltotal . " union all " . $sqlsex . " union all " . $sqlmedia);
        // dump($sqltotal);
        // dump($listtotal);
        // exit;
        // $total = $listtotal[0];
        // $nanke = $listtotal[1];
        // $fuke = $listtotal[2];
        // $wangluo = $listtotal[3];
        // unset($listtotal[0]);
        // unset($listtotal[1]);
        // unset($listtotal[2]);
        // unset($listtotal[3]);
        // if (in_array(1, explode(',', $meidatypeid))) {
        //     if ($projectid != 19) {
        //         array_unshift($listtotal, $fuke,$nanke);
        //     }
        //     array_unshift($listtotal, $wangluo);
        // }
        // if (session('sysuser.totaldata') == "是") {
        //     array_unshift($listtotal, $total);
        // }
        // var_dump($listtotal);
        sleep(0);
        return ($listtotal);
    }

    public function getIndexDataYuyueByMedia()
    {
        $projectid = session('projectid');
        $mediatype_id = input('mediatype_id');
        $sqlmedia = "SELECT sysmedia.`id` as id,
       sysmedia.`name`,
       sysmedia.`ename`,
       COUNT(CASE WHEN cases.`create_time` BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') THEN TRUE END) AS `jinriyuyue`,
       COUNT(CASE WHEN cases.`yuyuetime` BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') THEN TRUE END) AS `jinriyudao`,
       COUNT(CASE
                 WHEN cases.`statusdao` = '已到' AND cases.`yuyuetime` BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:59:59') THEN TRUE
             END) AS `jinrishidao`,
       COUNT(CASE
                 WHEN cases.`create_time` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `zuoriyuyue`,
       COUNT(CASE
                 WHEN cases.`yuyuetime` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `zuoriyudao`,
       COUNT(CASE
                 WHEN cases.`statusdao` = '已到' AND
                      cases.`yuyuetime` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 00:00:00') AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `zuorishidao`,
       COUNT(CASE
                 WHEN cases.`create_time` BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') THEN TRUE
             END) AS `benyueyuyue`,
       COUNT(CASE
                 WHEN cases.`yuyuetime` BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59') THEN TRUE
             END) AS `benyueyudao`,
       COUNT(CASE
                 WHEN cases.`statusdao` = '已到' AND cases.`yuyuetime` BETWEEN DATE_FORMAT(CURDATE(), '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(CURDATE()), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `benyueshidao`,
       COUNT(CASE
                 WHEN cases.`create_time` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `tongbiyuyue`,
       COUNT(CASE
                 WHEN cases.`yuyuetime` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `tongbiyudao`,
       COUNT(CASE
                 WHEN cases.`statusdao` = '已到' AND
                      cases.`yuyuetime` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `tongbishidao`,
       COUNT(CASE
                 WHEN cases.`create_time` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `shangyueyuyue`,
       COUNT(CASE
                 WHEN cases.`yuyuetime` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `shangyueyudao`,
       COUNT(CASE
                 WHEN cases.`statusdao` = '已到' AND
                      cases.`yuyuetime` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01 00:00:00') AND DATE_FORMAT(LAST_DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `shangyueshidao`,
       COUNT(CASE
                 WHEN cases.`yuyuetime` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 00:00:00') AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL -1 DAY), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `mingtianyudao`,
       COUNT(CASE
                 WHEN cases.`yuyuetime` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL -2 DAY), '%Y-%m-%d 00:00:00') AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL -2 DAY), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `houtianyudao`,
       COUNT(CASE
                 WHEN cases.`yuyuetime` BETWEEN DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL -3 DAY), '%Y-%m-%d 00:00:00') AND DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL -3 DAY), '%Y-%m-%d 23:59:59')
                     THEN TRUE
             END) AS `waitianyudao`
FROM `sysmedia`
     left join `case` as cases on sysmedia.id = cases.media_id
WHERE cases.`isdel` = 1 AND
      cases.project_id = $projectid AND
      sysmedia.id in (
          SELECT id
          from sysmedia
          where sysmedia.mediatype_id = $mediatype_id
      )
GROUP BY `media_id`";
        // var_dump($sqlmedia);
        // exit();
        $listmediatype = Db::query($sqlmedia);
        return $listmediatype;
    }
}
